/**
 * 
 */
package com.shine.icis.dao.imp;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Hibernate;

import com.shine.cotrun.sql.QueryParameters;
import com.shine.cotrun.sql.hibernate.CommQuery;
import com.shine.cotrun.web.comm.ListPage;
import com.shine.icis.dao.IStockQueryDAO;
import com.shine.icis.dao.imp.BaseHibernateDAO;
import com.shine.icis.dto.SeStock;
import com.shine.icis.dto.SeStockHis;
import com.shine.icis.dto.HsStock;
import com.shine.icis.dto.HsStockHis;

/**
 * @author zuohp
 * 
 */
public class StockQueryDAO extends BaseHibernateDAO implements IStockQueryDAO {
	
	/**
	 * 分页查询中登当前股份份额数据列表
	 * @author zuohp
	 * */
	@Override
	public ListPage<SeStock> findSeStockListPage(QueryParameters qp) {
		try {
			StringBuffer sbQueryCntString = new StringBuffer();	
			StringBuffer sbQueryString = new StringBuffer();
			sbQueryCntString.append("select count(1) num_ from eb_chk.stock_std_se ai where 1=1");
			sbQueryString.append("select seat_no, branch_no, exchange_type, ta_code, stock_account, stock_code ,fund_account, amount, src_table, busi_date from eb_chk.stock_std_se ai where 1=1");
			if(qp.getParameter("stockAccount") != null){
				sbQueryCntString.append(" and ai.stock_account = :stockAccount");
				sbQueryString.append(" and ai.stock_account = :stockAccount");
			}
			if(qp.getParameter("stockCode") != null){
				sbQueryCntString.append(" and ai.stock_code = :stockCode");
				sbQueryString.append(" and ai.stock_code = :stockCode");
			} 
			if(qp.getParameter("fundAccount") != null){
				sbQueryCntString.append(" and ai.fund_account = :fundAccount");
				sbQueryString.append(" and ai.fund_account = :fundAccount");
			}
			sbQueryCntString.append(" and rownum <= 10000");
			sbQueryString.append(" and rownum <= 10000");
			sbQueryString.append(" order by ai.stock_account, stock_code ");
			Query countQuery = getSession().createSQLQuery(sbQueryCntString.toString());
			Query query = getSession()
					.createSQLQuery(sbQueryString.toString())
					.addScalar("seat_no",Hibernate.STRING)
					.addScalar("branch_no",Hibernate.INTEGER)
					.addScalar("exchange_type",Hibernate.STRING)
					.addScalar("ta_code",Hibernate.STRING)
					.addScalar("stock_account",Hibernate.STRING)
					.addScalar("stock_code",Hibernate.STRING)
					.addScalar("fund_account",Hibernate.INTEGER)
					.addScalar("amount",Hibernate.DOUBLE)
					.addScalar("src_table",Hibernate.STRING)
					.addScalar("busi_date",Hibernate.INTEGER);
			
			ListPage<Object> page = new CommQuery<Object>().queryListPage(qp,
					query, countQuery);
			ListPage<SeStock> seStockPage = new ListPage<SeStock>();
			seStockPage.setTotalCount(page.getTotalCount());
			seStockPage.setCurrentPageNo(page.getCurrentPageNo());
			seStockPage.setCurrentPageSize(page.getCurrentPageSize());
			if (page != null && page.getDataList() != null) {
				List<SeStock> datas = new ArrayList<SeStock>();
				for (Object o : page.getDataList()) {
					Object[] oArr = (Object[]) o;
					SeStock seStock = new SeStock();
					
					if (oArr[0] == null){
						seStock.setSeatNo("");
					} else {
						seStock.setSeatNo(String.valueOf(oArr[0]));
					}
					seStock.setBranchNo(((Number) oArr[1]).intValue());
					seStock.setExchangeType(String.valueOf(oArr[2]));
					if (oArr[3] == null){
						seStock.setTaCode("");
					} else {
						seStock.setTaCode(String.valueOf(oArr[3]));
					}
					seStock.setStockAccount(String.valueOf(oArr[4]));
					seStock.setStockCode(String.valueOf(oArr[5]));
				    seStock.setFundAccount(((Number) oArr[6]).intValue());
					seStock.setAmount(((Number) oArr[7]).doubleValue());
					seStock.setSrcTable(String.valueOf(oArr[8]));
					if (oArr[9] == null){
						seStock.setBusiDate(null);
					} else {
						seStock.setBusiDate(((Number) oArr[9]).intValue());
					}
					datas.add(seStock);
				}
				seStockPage.setDataList(datas);
			}
			return seStockPage;
		} catch (RuntimeException re) {
			re.printStackTrace();
			return new ListPage<SeStock>();
		}
	}
	/**
	 * 分页查询中登历史股份份额数据列表
	 * @author zuohp
	 * */
	@Override
	public ListPage<SeStockHis> findSeStockHisListPage(QueryParameters qp) {
		try {
			StringBuffer sbQueryCntString = new StringBuffer();	
			StringBuffer sbQueryString = new StringBuffer();
			sbQueryCntString.append("select count(1) num_ from eb_chk_hist.vw_all_se_stock ai where 1=1");
			sbQueryString.append("select seat_no, branch_no, exchange_type, ta_code, stock_account, stock_code ,fund_account, amount, src_table, valid_date, invalid_date from eb_chk_hist.vw_all_se_stock ai where 1=1");
			if(qp.getParameter("stockAccount") != null){
				sbQueryCntString.append(" and ai.stock_account = :stockAccount");
				sbQueryString.append(" and ai.stock_account = :stockAccount");
			}
			if(qp.getParameter("stockCode") != null){
				sbQueryCntString.append(" and ai.stock_code = :stockCode");
				sbQueryString.append(" and ai.stock_code = :stockCode");
			} 
			if(qp.getParameter("fundAccount") != null){
				sbQueryCntString.append(" and ai.fund_account = :fundAccount");
				sbQueryString.append(" and ai.fund_account = :fundAccount");
			}
			if(qp.getParameter("stockAccount") == null && qp.getParameter("fundAccount") == null){
				if(qp.getParameter("queryDate") != null){
					sbQueryCntString.append(" and ai.valid_Date <= :queryDate and ai.invalid_date >= :queryDate");
					sbQueryString.append(" and ai.valid_Date <= :queryDate and ai.invalid_date >= :queryDate");
				} 
			}
			
			sbQueryCntString.append(" and rownum <= 10000");
			sbQueryString.append(" and rownum <= 10000");
			sbQueryString.append(" order by ai.stock_account, stock_code, valid_date, invalid_date ");
			Query countQuery = getSession().createSQLQuery(sbQueryCntString.toString());
			Query query = getSession()
					.createSQLQuery(sbQueryString.toString())
					.addScalar("seat_no",Hibernate.STRING)
					.addScalar("branch_no",Hibernate.INTEGER)
					.addScalar("exchange_type",Hibernate.STRING)
					.addScalar("ta_code",Hibernate.STRING)
					.addScalar("stock_account",Hibernate.STRING)
					.addScalar("stock_code",Hibernate.STRING)
					.addScalar("fund_account",Hibernate.INTEGER)
					.addScalar("amount",Hibernate.DOUBLE)
					.addScalar("src_table",Hibernate.STRING)
					.addScalar("valid_date",Hibernate.INTEGER)
			        .addScalar("invalid_date",Hibernate.INTEGER);
			 
			ListPage<Object> page = new CommQuery<Object>().queryListPage(qp,
					query, countQuery);
			ListPage<SeStockHis> seStockHisPage = new ListPage<SeStockHis>();
			seStockHisPage.setTotalCount(page.getTotalCount());
			seStockHisPage.setCurrentPageNo(page.getCurrentPageNo());
			seStockHisPage.setCurrentPageSize(page.getCurrentPageSize());
			if (page != null && page.getDataList() != null) {
				List<SeStockHis> datas = new ArrayList<SeStockHis>();
				for (Object o : page.getDataList()) {
					Object[] oArr = (Object[]) o;
					SeStockHis seStockHis = new SeStockHis();
					
					if (oArr[0] == null){
						seStockHis.setSeatNo("");
					} else {
						seStockHis.setSeatNo(String.valueOf(oArr[0]));
					}
					seStockHis.setBranchNo(((Number) oArr[1]).intValue());
					seStockHis.setExchangeType(String.valueOf(oArr[2]));
					if (oArr[3] == null){
						seStockHis.setTaCode("");
					} else {
						seStockHis.setTaCode(String.valueOf(oArr[3]));
					}
					seStockHis.setStockAccount(String.valueOf(oArr[4]));
					seStockHis.setStockCode(String.valueOf(oArr[5]));
					seStockHis.setFundAccount(((Number) oArr[6]).intValue());
					seStockHis.setAmount(((Number) oArr[7]).doubleValue());
					seStockHis.setSrcTable(String.valueOf(oArr[8]));
					seStockHis.setValidDate(((Number) oArr[9]).intValue());
					seStockHis.setInvalidDate(((Number) oArr[10]).intValue());
					datas.add(seStockHis);
				}
				seStockHisPage.setDataList(datas);
			}
			return seStockHisPage;
		} catch (RuntimeException re) {
			re.printStackTrace();
			return new ListPage<SeStockHis>();
		}
	}
	/**
	 * 分页查询恒生当前股份份额数据列表
	 * @author zuohp
	 * */
	@Override
	public ListPage<HsStock> findHsStockListPage(QueryParameters qp) {
		try {
			StringBuffer sbQueryCntString = new StringBuffer();	
			StringBuffer sbQueryString = new StringBuffer();
			sbQueryCntString.append("select count(1) num_ from eb_chk.vw_hs_stock ai where 1=1");
			sbQueryString.append("select branch_no, exchange_type, ta_code, stock_account, stock_code ,fund_account, stock_type, current_amount, etl_busi_date from eb_chk.vw_hs_stock ai where 1=1");
			if(qp.getParameter("stockAccount") != null){
				sbQueryCntString.append(" and ai.stock_account = :stockAccount");
				sbQueryString.append(" and ai.stock_account = :stockAccount");
			}
			if(qp.getParameter("stockCode") != null){
				sbQueryCntString.append(" and ai.stock_code = :stockCode");
				sbQueryString.append(" and ai.stock_code = :stockCode");
			} 
			if(qp.getParameter("fundAccount") != null){
				sbQueryCntString.append(" and ai.fund_account = :fundAccount");
				sbQueryString.append(" and ai.fund_account = :fundAccount");
			}
			sbQueryCntString.append(" and rownum <= 10000");
			sbQueryString.append(" and rownum <= 10000");
			sbQueryString.append(" order by ai.stock_account, stock_code ");
			Query countQuery = getSession().createSQLQuery(sbQueryCntString.toString());
			Query query = getSession()
					.createSQLQuery(sbQueryString.toString())
					.addScalar("branch_no",Hibernate.INTEGER)
					.addScalar("exchange_type",Hibernate.STRING)
					.addScalar("ta_code",Hibernate.STRING)
					.addScalar("stock_account",Hibernate.STRING)
					.addScalar("stock_code",Hibernate.STRING)
					.addScalar("fund_account",Hibernate.INTEGER)
					.addScalar("stock_type",Hibernate.STRING)
					.addScalar("current_amount",Hibernate.DOUBLE)
					.addScalar("etl_busi_date",Hibernate.INTEGER);
			
			ListPage<Object> page = new CommQuery<Object>().queryListPage(qp,
					query, countQuery);
			ListPage<HsStock> hsStockPage = new ListPage<HsStock>();
			hsStockPage.setTotalCount(page.getTotalCount());
			hsStockPage.setCurrentPageNo(page.getCurrentPageNo());
			hsStockPage.setCurrentPageSize(page.getCurrentPageSize());
			if (page != null && page.getDataList() != null) {
				List<HsStock> datas = new ArrayList<HsStock>();
				for (Object o : page.getDataList()) {
					Object[] oArr = (Object[]) o;
					HsStock hsStock = new HsStock();
					
					hsStock.setBranchNo(((Number) oArr[0]).intValue());
					hsStock.setExchangeType(String.valueOf(oArr[1]));
					if (oArr[2] == null){
						hsStock.setTaCode("");
					} else {
						hsStock.setTaCode(String.valueOf(oArr[2]));
					}
					hsStock.setStockAccount(String.valueOf(oArr[3]));
					hsStock.setStockCode(String.valueOf(oArr[4]));
					hsStock.setFundAccount(((Number) oArr[5]).intValue());
					hsStock.setStockType(String.valueOf(oArr[6]));
					hsStock.SetCurrentAmount(((Number) oArr[7]).doubleValue());
					hsStock.setBusiDate(((Number) oArr[8]).intValue());
					datas.add(hsStock);
				}
				hsStockPage.setDataList(datas);
			}
			return hsStockPage;
		} catch (RuntimeException re) {
			re.printStackTrace();
			return new ListPage<HsStock>();
		}
	}
	/**
	 * 分页查询恒生历史股份份额数据列表
	 * @author zuohp
	 * */
	@Override
	public ListPage<HsStockHis> findHsStockHisListPage(QueryParameters qp) {
		try {
			StringBuffer sbQueryCntString = new StringBuffer();	
			StringBuffer sbQueryString = new StringBuffer();
			sbQueryCntString.append("select count(1) num_ from eb_chk_hist.vw_hs_stock ai where 1=1");
			sbQueryString.append("select branch_no, exchange_type, ta_code, stock_account, stock_code ,fund_account, stock_type, current_amount,  valid_date, invalid_date from eb_chk_hist.vw_hs_stock ai where 1=1");
			if(qp.getParameter("stockAccount") != null){
				sbQueryCntString.append(" and ai.stock_account = :stockAccount");
				sbQueryString.append(" and ai.stock_account = :stockAccount");
			}
			if(qp.getParameter("stockCode") != null){
				sbQueryCntString.append(" and ai.stock_code = :stockCode");
				sbQueryString.append(" and ai.stock_code = :stockCode");
			} 
			if(qp.getParameter("fundAccount") != null){
				sbQueryCntString.append(" and ai.fund_account = :fundAccount");
				sbQueryString.append(" and ai.fund_account = :fundAccount");
			}
			if(qp.getParameter("stockAccount") == null && qp.getParameter("fundAccount") == null){
				if(qp.getParameter("queryDate") != null){
					sbQueryCntString.append(" and ai.valid_Date <= :queryDate and ai.invalid_date >= :queryDate");
					sbQueryString.append(" and ai.valid_Date <= :queryDate and ai.invalid_date >= :queryDate");
				} 
			}
			
			sbQueryCntString.append(" and rownum <= 10000");
			sbQueryString.append(" and rownum <= 10000");
			sbQueryString.append(" order by ai.stock_account, stock_code, valid_date, invalid_date ");
			Query countQuery = getSession().createSQLQuery(sbQueryCntString.toString());
			Query query = getSession()
					.createSQLQuery(sbQueryString.toString())
					.addScalar("branch_no",Hibernate.INTEGER)
					.addScalar("exchange_type",Hibernate.STRING)
					.addScalar("ta_code",Hibernate.STRING)
					.addScalar("stock_account",Hibernate.STRING)
					.addScalar("stock_code",Hibernate.STRING)
					.addScalar("fund_account",Hibernate.INTEGER)
					.addScalar("stock_type",Hibernate.STRING)
					.addScalar("current_amount",Hibernate.DOUBLE)
					.addScalar("valid_date",Hibernate.INTEGER)
					.addScalar("invalid_date",Hibernate.INTEGER);
			
			ListPage<Object> page = new CommQuery<Object>().queryListPage(qp,
					query, countQuery);
			ListPage<HsStockHis> hsStockHisPage = new ListPage<HsStockHis>();
			hsStockHisPage.setTotalCount(page.getTotalCount());
			hsStockHisPage.setCurrentPageNo(page.getCurrentPageNo());
			hsStockHisPage.setCurrentPageSize(page.getCurrentPageSize());
			if (page != null && page.getDataList() != null) {
				List<HsStockHis> datas = new ArrayList<HsStockHis>();
				for (Object o : page.getDataList()) {
					Object[] oArr = (Object[]) o;
					HsStockHis hsStockHis = new HsStockHis();
					hsStockHis.setBranchNo(((Number) oArr[0]).intValue());
					hsStockHis.setExchangeType(String.valueOf(oArr[1]));
					if (oArr[2] == null){
						hsStockHis.setTaCode("");
					} else {
						hsStockHis.setTaCode(String.valueOf(oArr[2]));
					}
					hsStockHis.setStockAccount(String.valueOf(oArr[3]));
					hsStockHis.setStockCode(String.valueOf(oArr[4]));
					hsStockHis.setFundAccount(((Number) oArr[5]).intValue());
					hsStockHis.setStockType(String.valueOf(oArr[6]));
					hsStockHis.SetCurrentAmount(((Number) oArr[7]).doubleValue());
					hsStockHis.setValidDate(((Number) oArr[8]).intValue());
					hsStockHis.setInvalidDate(((Number) oArr[9]).intValue());
					datas.add(hsStockHis);
				}
				hsStockHisPage.setDataList(datas);
			}
			return hsStockHisPage;
		} catch (RuntimeException re) {
			re.printStackTrace();
			return new ListPage<HsStockHis>();
		}
	}
}
